package com.jydw.da.frame.utils;

import cn.hutool.extra.spring.SpringUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.core.env.Environment;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Objects;

@Slf4j
public class ShardingAlgorithmTool {
    private static final HashSet<String> tableNameCache = new HashSet<>();

    /**
     * 判断 分表获取的表名是否存在 不存在则自动建表
     *
     * @param logicTableName  逻辑表名(表头)
     * @param resultTableName 真实表名
     * @return 确认存在于数据库中的真实表名
     */
    public static String shardingTablesCheckAndCreatAndReturn(String logicTableName, String resultTableName) {
        synchronized (logicTableName.intern()) {
            // 缓存中有此表 返回
            if (tableNameCache.contains(resultTableName)) {
                return resultTableName;
            }
            // 缓存中无此表 建表 并添加缓存
            List<String> sqlList = selectTableCreateSql(logicTableName);
            sqlList.replaceAll(s -> s.replace("CREATE TABLE", "CREATE TABLE IF NOT EXISTS").replace(logicTableName, resultTableName));
            executeSql(sqlList);
            tableNameCache.add(resultTableName);
        }
        return resultTableName;
    }

    /**
     * 缓存重载方法
     */
    public static void tableNameCacheReload() {
        // 读取数据库中所有表名
        List<String> tableNameList = getAllTableNameBySchema();
        // 删除旧的缓存(如果存在)
        ShardingAlgorithmTool.tableNameCache.clear();
        // 写入新的缓存
        ShardingAlgorithmTool.tableNameCache.addAll(tableNameList);
    }


    private static void executeSql(List<String> sqlList) {
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://10.139.44.193:3306/cool1?useSSL=false&character_set_server=utf8mb4", "root", "Cyj@1998")) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
                conn.commit();
            } catch (Exception ex) {
                conn.rollback();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        try (Connection conn = DriverManager.getConnection("jdbc:mysql://10.139.44.193:3306/cool2?useSSL=false&character_set_server=utf8mb4", "root", "Cyj@1998")) {
            try (Statement st = conn.createStatement()) {
                conn.setAutoCommit(false);
                for (String sql : sqlList) {
                    st.execute(sql);
                }
                conn.commit();
            } catch (Exception ex) {
                conn.rollback();
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private static List<String> selectTableCreateSql(String tableName) {
        List<String> res = new ArrayList<>();
        if ("t_log".equals(tableName)) {
            res.add("CREATE TABLE `t_log` ( " +
                    "  `id` bigint unsigned NOT NULL, " +
                    "  `username` varchar(32) NOT NULL  COMMENT '用户名', " +
                    "  `password` varchar(32) NOT NULL COMMENT '密码', " +
                    "  `role_id` bigint NOT NULL DEFAULT '0' COMMENT '角色id', " +
                    "  `parse_time` bigint NOT NULL DEFAULT '0' COMMENT '解析时间', " +
                    "  `create_time` datetime DEFAULT NULL COMMENT '创建时间', " +
                    "  PRIMARY KEY (`id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='日志表'");
            res.add("ALTER TABLE `t_log` ADD INDEX n1 ( `create_time`, `username` ) USING BTREE");
            res.add("ALTER TABLE `t_log` ADD INDEX n2 ( `username` ) USING BTREE");
            res.add("ALTER TABLE `t_log` ADD INDEX n3 ( `parse_time` ) USING BTREE");
        }
        return res;
    }

    //初始化执行这个方法，查找库中所有的t_log
    public static List<String> getAllTableNameBySchema() {
        List<String> res = new ArrayList<>();
        Environment env = SpringUtil.getApplicationContext().getEnvironment();
        try (Connection connection = DriverManager.getConnection("jdbc:mysql://10.139.44.193:3306/cool2?useSSL=false&character_set_server=utf8mb4", "root", "Cyj@1998");
             Statement st = connection.createStatement()) {
            try (ResultSet rs = st.executeQuery("SHOW TABLES like 't_log%'")) {
                while (rs.next()) {
                    res.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

    public static HashSet<String> cacheTableNames() {
        return tableNameCache;
    }
}
